﻿Public Class CopyBatch
    Sub GetTopFct(username As String, PlanYear As Decimal, FctGroup As String, ByRef fctCode As String, ByRef version As Integer)
        Dim adapter As New FundCenter2TableAdapters.FundCenterHierarchiesTableAdapter
        Dim datatable As New FundCenter2.FundCenterHierarchiesDataTable
        datatable = adapter.GetTopFctByUser(username, PlanYear, FctGroup)
        'Response.Write(username & "/" & PlanYear & "/" & FctGroup)
        Try
            If datatable.Rows.Count > 0 Then
                For Each row As FundCenter2.FundCenterHierarchiesRow In datatable
                    fctCode = row.FundCenter_FundCenterCode
                    Dim level As Integer = Val(Left(row.OrgLevel, 1))
                    version = level
                Next
            End If
        Catch ex As Exception
            fctCode = ""
            version = 1
        End Try
    End Sub
    Sub GetChildWithVersion(FundCenterCode As String, PlanYear As Decimal, FctGroup As String, ByRef ds As DataSet, ByRef CopyToVersion As Integer)


        Dim dt As DataTable = ds.Tables.Add("Items")
        dt.Columns.Add("FundCenterCode", Type.GetType("System.String"))
        dt.Columns.Add("FundCenterName", Type.GetType("System.String"))
        dt.Columns.Add("now_version", Type.GetType("System.Int32"))
        dt.Columns.Add("level", Type.GetType("System.Int32"))

        dt.Columns.Add("CreateDate", Type.GetType("System.String"))
        dt.Columns.Add("ReleaseFlag", Type.GetType("System.String"))
        Dim Radapter As New FundCenterRelationTableAdapters.FundCenterReleasesTableAdapter
        Dim Rdatatable As New FundCenterRelation.FundCenterReleasesDataTable
        Dim Fadapter As New FundCenterRelationTableAdapters.FundCenterHierarchiesTableAdapter
        Dim Fdatatable As New FundCenterRelationTableAdapters.FundCenterHierarchiesTableAdapter
        Dim Hadapter As New FundCenterRelationTableAdapters.FctHTableAdapter
        Dim Hdatatable As New FundCenterRelation.FctHDataTable
        Dim Cdatatable As New FundCenterRelation.FctHDataTable
        Dim Badapter As New FundCenterRelationTableAdapters.BudgetDetailsTableAdapter

        Hdatatable = Hadapter.GetDataByFctCode(FundCenterCode, PlanYear, FctGroup)
        Dim RootGroupPath As String = ""

        If Hdatatable.Count = 0 Then
            Exit Sub
        End If
        Dim r As DataRow
        'add root
        For Each Hrow As FundCenterRelation.FctHRow In Hdatatable
            RootGroupPath = Hrow.grouppath
            r = dt.NewRow()
            r("FundCenterCode") = Hrow.FundCenter_FundCenterCode
            r("FundCenterName") = Hrow.Description
            r("level") = Val(Left(Fadapter.GetOrgLevel(Hrow.FundCenter_FundCenterCode, PlanYear, FctGroup), 1))
            'level=Org Level
            CopyToVersion = r("level")
            'Now version fix =1
            r("now_version") = 1
            'fundcenterreleases table 
            Rdatatable = Radapter.GetDataByFCT(r("FundCenterCode"), PlanYear, r("now_version"))

            r("CreateDate") = ""
            r("ReleaseFlag") = ""
            For Each rrow As FundCenterRelation.FundCenterReleasesRow In Rdatatable
                r("CreateDate") = If(rrow.IsUpdatedDateNull, rrow.CreatedDate, rrow.UpdatedDate)
                r("ReleaseFlag") = rrow.ReleaseFlag
            Next
            If r("ReleaseFlag") = "" Then
                'Budgetdeatail table
                If Badapter.CountRecord(r("FundCenterCode"), PlanYear, r("now_version")) > 0 Then
                    r("ReleaseFlag") = "N"
                End If
            End If
            dt.Rows.Add(r)
        Next
        'get child 1 level
        Hdatatable = Hadapter.GetDataByGroupPath(RootGroupPath & "__", PlanYear, FctGroup)
        Dim version As Integer
        'add in temp Table
        For Each row As FundCenterRelation.FctHRow In Hdatatable
            r = dt.NewRow()
            r("FundCenterCode") = row.FundCenter_FundCenterCode
            r("FundCenterName") = row.Description
            'level=Org Level
            r("level") = Val(Left(Fadapter.GetOrgLevel(row.FundCenter_FundCenterCode, PlanYear, FctGroup), 1))
            'get child all
            Cdatatable = Hadapter.GetDataByGroupPath(row.grouppath & "%", PlanYear, FctGroup)
            If Cdatatable.Count = 0 Then 'If child have = 0
                r("now_version") = 1 'now version is 1
            Else
                r("now_version") = r("level") 'now version is Orglevel
            End If
            version = r("now_version")
            'FundCenterReleasesTableAdapter
            Rdatatable = Radapter.GetDataByFCT(r("FundCenterCode"), PlanYear, r("now_version"))

            r("CreateDate") = ""
            r("ReleaseFlag") = ""
            For Each rrow As FundCenterRelation.FundCenterReleasesRow In Rdatatable

                r("CreateDate") = If(rrow.IsUpdatedDateNull, rrow.CreatedDate, rrow.UpdatedDate)
                r("ReleaseFlag") = rrow.ReleaseFlag
            Next
            If r("ReleaseFlag") = "" Then
                If Badapter.CountRecord(r("FundCenterCode"), PlanYear, r("now_version")) > 0 Then
                    r("ReleaseFlag") = "N"
                End If
            End If

            dt.Rows.Add(r)
            'insert child
            For Each crow As FundCenterRelation.FctHRow In Cdatatable
                r = dt.NewRow()
                r("FundCenterCode") = crow.FundCenter_FundCenterCode
                r("FundCenterName") = crow.Description

                r("level") = Val(Left(Fadapter.GetOrgLevel(crow.FundCenter_FundCenterCode, PlanYear, FctGroup), 1))

                r("now_version") = version

                Rdatatable = Radapter.GetDataByFCT(r("FundCenterCode"), PlanYear, r("now_version"))

                r("CreateDate") = ""
                r("ReleaseFlag") = ""
                For Each rrow As FundCenterRelation.FundCenterReleasesRow In Rdatatable
                    r("CreateDate") = If(rrow.IsUpdatedDateNull, rrow.CreatedDate, rrow.UpdatedDate)
                    r("ReleaseFlag") = rrow.ReleaseFlag
                Next
                If r("ReleaseFlag") = "" Then
                    If Badapter.CountRecord(r("FundCenterCode"), PlanYear, r("now_version")) > 0 Then
                        r("ReleaseFlag") = "N"
                    End If
                End If
                dt.Rows.Add(r)
            Next



        Next

    End Sub
    Function BatchInsert(ByVal planyear As Short, ByVal re_flag As String, ByVal last_flag As String, ByVal version As String, ByVal prev As String, ByVal create_date As DateTime, ByVal createby As String, ByVal fctr As String, ByVal fctr_year As Short) As String
        Dim sql As String = ""
        Dim credate As String = ConvertDateTimeTS(create_date)
        sql += " INSERT INTO FundCenterReleases (PlanYear, ReleaseFlag, LastVersionFlag, Version, PrevVersion, CreatedDate, CreatedBy, FundCenter_FundCenterCode, FundCenter_PlanYear)"
        sql += " VALUES (" & planyear & ",'" & re_flag & "','" & last_flag & "','" & version & "','" & prev & "'," & credate & ",'" & createby & "','" & fctr & "'," & fctr_year & ")"
        sql += "; "
        Return sql
    End Function
    Function BatchForceFlag(ByVal re_flag As String, ByVal up_date As DateTime, ByVal up_by As String, ByVal forceflag As String, ByVal remark As String, ByVal fctr As String, ByVal pyear As Short, ByVal vers As String) As String
        Dim sql As String = ""
        Dim udate As String = ConvertDateTimeTS(up_date)
        sql += " UPDATE FundCenterReleases"
        sql += " SET  ReleaseFlag = '" & re_flag & "', UpdatedDate = " & udate & ", UpdatedBy = '" & up_by & "', ForceFlag = '" & forceflag & "', Remark = '" & remark & "'"
        sql += " WHERE  (FundCenter_FundCenterCode = '" & fctr & "') AND (FundCenter_PlanYear = " & pyear & ") AND (Version = '" & vers & "')"
        Return sql
    End Function
    Function BatchcopyAtt(ByVal new_id As String, ByVal old_bid As String) As String
        Dim sql As String = ""
        sql += " INSERT INTO Attachments SELECT ItemNo, Title, FileName, FileType, FileSize, MimeType, CreatedDate, UpdatedDate, CreatedBy, UpdatedBy, " & new_id & " AS BudgetDetail_Id, delete_flag"
        sql += " FROM  Attachments AS Attachments_1"
        sql += " WHERE (BudgetDetail_Id = " & old_bid & ")"
        sql += "; "
        Return sql
    End Function
    Function ConvertDateTimeTS(ByVal InputDate As Date) As String

        Dim sDate, dDate, mDate, yDate, tDate, nowDate As String

        Dim format As [String] = "dd/MM/yyyy HH:mm:ss"
        sDate = InputDate.ToString(format)
        dDate = Mid(sDate, 1, 2)
        mDate = Mid(sDate, 4, 2)
        yDate = Mid(sDate, 7, 4)
        If Val(yDate) >= 2550 Then
            yDate = (Val(yDate) - 543).ToString
        End If
        tDate = Mid(sDate, 12, 8)
        nowDate = "{ts '" & yDate & "-" & mDate & "-" & dDate & " " & tDate & "'}"
        Return nowDate
    End Function
    Function BatchcopyB12(ByVal newver As String, ByVal new_id As String, ByVal old_bid As String) As String
        Dim sql As String = ""
        sql += " INSERT INTO  Budget12  SELECT  PlanYear, BaseYear, '" & newver & "' as Version, FunctionalArea, FundedProgram, Amount1, Amount2, Amount3, Amount4, Amount5, Amount6, Amount7, Amount8, Amount9, "
        sql += " Amount10, Amount11, Amount12, ForecastYear1, ForecastYear2, ForecastYear3, ForecastYear4, ProductItemNo, CreatedDate, UpdatedDate, CreatedBy, UpdatedBy, "
        sql += " FundCenter_FundCenterCode, FundCenter_PlanYear, Fund_FundCode, Fund_PlanYear, Cmmt_CmmtCode, Cmmt_PlanYear, CmmtDetail_Id, ActivityType_PlanYear, "
        sql += " ActivityType_ActivityTypeCode, Activity_ActivityCode, Activity_PlanYear, Product_ProductCode, Product_PlanYear, Project_ProjectCode, Project_PlanYear, "
        sql += " Project_FundCenterCode, " & new_id & " as BudgetDetail_Id"
        sql += " FROM Budget12"
        sql += " WHERE        (BudgetDetail_Id = " & old_bid & ")"
        sql += "; "
        Return sql
    End Function
    Function BatchCopyRev(ByVal vers As String, ByVal r_id As String) As String
        Dim sql As String = ""
        sql += " INSERT INTO BudgetRevPlans   SELECT         PlanYear, BaseYear,'" & vers & "' as Version, ActualLastYear2, ActualLastYear1, ForecastLastYear1, ForecastCurYear, LastYearMonth1, LastYearMonth2, LastYearMonth3, "
        sql += " LastYearMonth4, LastYearMonth5, LastYearMonth6, LastYearMonth7, LastYearMonth8, LastYearMonth9, LastYearMonth10, LastYearMonth11, LastYearMonth12,"
        sql += " CurYearMonth1, CurYearMonth2, CurYearMonth3, CurYearMonth4, CurYearMonth5, CurYearMonth6, CurYearMonth7, CurYearMonth8, CurYearMonth9,"
        sql += " CurYearMonth10, CurYearMonth11, CurYearMonth12, CreatedDate, UpdatedDate, CreatedBy, UpdatedBy, FundCenter_FundCenterCode, FundCenter_PlanYear,"
        sql += " Product_ProductCode, Product_PlanYear, ProductItem_Id"
        sql += " FROM BudgetRevPlans"
        sql += " WHERE        (Id = " & r_id & ")"
        Return sql
    End Function
    Function BatchForce(ByVal planyear As Short, ByVal re_flag As String, ByVal last_flag As String, ByVal version As String, ByVal prev As String, ByVal create_date As DateTime, ByVal createby As String, ByVal fctr As String, ByVal fctr_year As Short, ByVal ForceFlag As String, ByVal ReleaseReason As String) As String
        Dim sql As String = ""
        Dim credate As String = ConvertDateTimeTS(create_date)
        sql += " INSERT INTO FundCenterReleases (PlanYear, ReleaseFlag, LastVersionFlag, Version, PrevVersion, CreatedDate, CreatedBy, FundCenter_FundCenterCode, FundCenter_PlanYear,ForceFlag,ReleaseReason)"
        sql += " VALUES (" & planyear & ",'" & re_flag & "','" & last_flag & "','" & version & "','" & prev & "'," & credate & ",'" & createby & "','" & fctr & "'," & fctr_year & ",'" & ForceFlag & "','" & ReleaseReason & "')"
        sql += "; "
        Return sql
    End Function
    Function BatchOldRelease(ByVal lastver As String, ByVal updatedate As DateTime, ByVal updateby As String, ByVal fctr As String, ByVal fctryear As Short, ByVal newver As String) As String
        Dim sql As String = ""
        Dim udate As String = ConvertDateTimeTS(updatedate)
        sql += " UPDATE FundCenterReleases"
        sql += " SET LastVersionFlag = '" & lastver & "', UpdatedDate = " & udate & ", UpdatedBy = '" & updateby & "'"
        sql += " WHERE (FundCenter_FundCenterCode = '" & fctr & "') AND (FundCenter_PlanYear = " & fctryear & ") "
        sql += " AND Version = '" & newver & "' "
        Return sql
    End Function
    Function BatchOldReleaseCopy(ByVal lastver As String, ByVal updatedate As DateTime, ByVal updateby As String, ByVal fctr As String, ByVal fctryear As Short) As String 'Add By Orn 14/08/2558
        Dim sql As String = ""
        Dim udate As String = ConvertDateTimeTS(updatedate)
        sql += " UPDATE FundCenterReleases"
        sql += " SET LastVersionFlag = '" & lastver & "', UpdatedDate = " & udate & ", UpdatedBy = '" & updateby & "'"
        sql += " WHERE (FundCenter_FundCenterCode = '" & fctr & "') AND (FundCenter_PlanYear = " & fctryear & ") "
        sql += " AND Version = (Select version from FundCenterReleases "
        sql += " where FundCenter_FundCenterCode = '" & fctr & "' "
        sql += " AND FundCenter_PlanYear = " & fctryear
        sql += " AND LastVersionFlag ='Y') "
        Return sql
    End Function
End Class
